package com.huayanginfo.saas.export;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringEscapeUtils;

/**
 * 根据建表sql，创建存储过程。换言之，saas的租户数据库由存储过程创建，而数据库在开发阶段经常变化，这个类可以直接通过建表sql创建存储过程。
 * 支持mysql workbench导出的sql脚本。
 * @author rabbit
 *
 */
public class CreateDBProcedure {
	
	private static final String newLine = "\n";
	private static final String HEADER = "CREATE  TABLE IF NOT EXISTS ";
	/**
	 * 
	 * @param createTableSql
	 * @return
	 */
	public static String formatCode (String createTableSql) {
		String fistPart = null;
		String lastPart = null;
		String model = (HEADER);
		if (createTableSql.startsWith(model) && createTableSql.endsWith(";")) {
			String sql = StringEscapeUtils.escapeSql(createTableSql);
			int index = model.length();
			fistPart = sql.substring(0, index);
			lastPart = sql.substring(index, sql.length());
			StringBuilder sbBuilder = new StringBuilder("SET @s = CONCAT('");
			sbBuilder.append(fistPart).append("', dbname, '.").append(lastPart).append("');")
			.append(newLine).append("PREPARE stmt FROM @s; EXECUTE stmt;DEALLOCATE PREPARE stmt; ").append(newLine).append(newLine);
			return sbBuilder.toString();
		} else  {
			return null;
		}
	}
	
	public static String getCreatePrepareSql() {
		StringBuilder sb = new StringBuilder();
		sb.append("Drop procedure if exists create_database;").append(newLine);
		sb.append("CREATE PROCEDURE `create_database`(IN `dbname` varCHAR(100), In  `ip` varCHAR(15), In  `username` varCHAR(100), In  `passwd` varCHAR(100), In  `firstname` varCHAR(100),In  `lastname` varCHAR(100), In  `email` varCHAR(100))").append(newLine);
		sb.append("LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER").append(newLine);
		sb.append("BEGIN").append(newLine);
		sb.append("SET @s = CONCAT('create database ', dbname); PREPARE stmt FROM @s; EXECUTE stmt;DEALLOCATE PREPARE stmt;").append(newLine);
		sb.append(getPrivelige());
		return sb.toString();
	}
	
	private static String getInsert() {
		String insert = "SET @s = CONCAT('insert into ', dbname, '.users(email,last_name, first_name) values (''',email,''',''',lastname, ''',''',firstname, ''')');";
		StringBuilder sBuilder = new StringBuilder(insert);
		sBuilder.append(newLine).append("PREPARE stmt FROM @s; EXECUTE stmt;DEALLOCATE PREPARE stmt;").append(newLine).append(newLine);
		return sBuilder.toString();
	}

	public static String getEndSql() {
		return getInsert() + "END;";
	}
	
	/**
	 * sqls must be start with CREATE TABLE and end with;
	 * @param inputSql
	 * @return
	 */
	public static String getMiddleDetailSql(String inputSql) {
		Pattern pattern = Pattern.compile("(" + HEADER + ".*?;)?", Pattern.DOTALL);
		Matcher matcher = pattern.matcher(inputSql);
		StringBuilder sBuilder = new StringBuilder();
		while (matcher.find()) {
			String createTableSql = matcher.group();
			if (!createTableSql.isEmpty()) {
				sBuilder.append(formatCode(matcher.group()));
			}
		}
		return sBuilder.toString();
	}
	
	public static void main(String[] args) throws IOException {
		String basePath = CreateDBProcedure.class.getPackage().getName().replaceAll("\\.", "/");
		String sqls = ResourceReader.getString("/" + basePath + "/create.sql", null);
		StringBuilder stringBuilder = new StringBuilder(getCreatePrepareSql());
		stringBuilder.append(getMiddleDetailSql(sqls));
		stringBuilder.append(getEndSql());
		System.out.println(stringBuilder.toString());
	}

	private static String getPrivelige() {
		StringBuilder builder = new StringBuilder();
		String s = "SET @s = CONCAT('grant SELECT,UPDATE,DELETE,CREATE TEMPORARY TABLES,INDEX,INSERT,LOCK TABLES on ',dbname,'.* to ''', username, '''@''', ip, ''' identified by ''' , passwd,  '''');";
		builder.append(s).append(newLine).append(newLine).append("PREPARE stmt FROM @s; EXECUTE stmt;DEALLOCATE PREPARE stmt;").append(newLine).append(newLine);
		return builder.toString();
	}
}
